﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AutoMapper;
using ThaireInvestmentWeb.ExcelWSService;
using ThaireInvestmentWeb.Models.criteria;
using ThaireInvestmentWeb.ReportServiceRef;

//http://localhost:43462/ReportR3/Form1?reportCode=3302

namespace ThaireInvestmentWeb.Controllers.Report.R3
{
    public class ReportR3Controller : Controller
    {
        //
        // GET: /ReportR3/

        public ActionResult Form1(string reportCode)
        {



            ViewBag.reportCode = reportCode;
            if (reportCode.StartsWith("ว"))
            {
                ViewBag.mode = 1;
            }
            else
            {
                ViewBag.mode = 2;
            }

            if (reportCode == "ว3302")
            {// ชื่อ report piority 1
                ViewBag.reportName = "รายงานเงินลงทุนในพันธบัตร – ตั๋วเงินคลัง";
            }
            else if (reportCode == "ว3100")
            {
                ViewBag.reportName = "รายงานรายได้จากการลงทุนสุทธิและรายได้จากการลงทุนค้างรับ";
            }
            else if (reportCode == "ว3200")
            {
                ViewBag.reportName = "รายงานกำไร (ขาดทุน) จากการจำหน่าย - การโอนเปลี่ยนประเภท – การเพิ่มหรือลดค่าจากการตีราคาเงินลงทุนในหลักทรัพย์";
            }
            else if (reportCode == "ว3300")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหลักทรัพย์";
            }
            else if (reportCode == "ว3301")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในตราสารหนี้เผื่อขายและที่จะถือจนครบกำหนด – จัดตามระยะเวลาการครบกำหนด";
            }
            else if (reportCode == "ว3303")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในตั๋วสัญญาใช้เงิน – ตั๋วแลกเงิน";
            }
            else if (reportCode == "ว3305")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหุ้นทุน";
            }
            else if (reportCode == "ว3306")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหุ้นกู้-หุ้นกู้แปลงสภาพ";
            }
            else if (reportCode == "ว3307")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหน่วยลงทุน";
            }
            else if (reportCode == "ว3520")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีทรัพย์สินจำนองเป็นประกัน";
            }
            else if (reportCode == "ว3521")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีอสังหาริมทรัพย์จำนองเป็นประกัน – เพื่อที่อยู่อาศัย";
            }
            else if (reportCode == "ว3570")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีบุคคลค้ำประกัน/ให้กู้ยืมแก่สหกรณ์";
            }
            else if (reportCode == "ว3710")
            {
                ViewBag.reportName = "รายงานเงินสดและเงินฝากสถาบันการเงิน";
            }

            // --- report ช
            if (reportCode == "ช3302")
            {// ชื่อ report piority 1
                ViewBag.reportName = "รายงานเงินลงทุนในพันธบัตร – ตั๋วเงินคลัง";
            }
            else if (reportCode == "ช3100")
            {
                ViewBag.reportName = "รายงานรายได้จากการลงทุนสุทธิและรายได้จากการลงทุนค้างรับ";
            }
            else if (reportCode == "ช3200")
            {
                ViewBag.reportName = "รายงานกำไร (ขาดทุน) จากการจำหน่าย - การโอนเปลี่ยนประเภท – การเพิ่มหรือลดค่าจากการตีราคาเงินลงทุนในหลักทรัพย์";
            }
            else if (reportCode == "ช3300")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหลักทรัพย์";
            }
            else if (reportCode == "ช3301")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในตราสารหนี้เผื่อขายและที่จะถือจนครบกำหนด – จัดตามระยะเวลาการครบกำหนด";
            }
            else if (reportCode == "ช3303")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในตั๋วสัญญาใช้เงิน – ตั๋วแลกเงิน";
            }
            else if (reportCode == "ช3305")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหุ้นทุน";
            }
            else if (reportCode == "ช3306")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหุ้นกู้-หุ้นกู้แปลงสภาพ";
            }
            else if (reportCode == "ว3307")
            {
                ViewBag.reportName = "รายงานเงินลงทุนในหน่วยลงทุน";
            }
            else if (reportCode == "ช3520")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีทรัพย์สินจำนองเป็นประกัน";
            }
            else if (reportCode == "ช3521")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีอสังหาริมทรัพย์จำนองเป็นประกัน – เพื่อที่อยู่อาศัย";
            }
            else if (reportCode == "ว3570")
            {
                ViewBag.reportName = "รายงานเงินให้กู้ยืมโดยมีบุคคลค้ำประกัน/ให้กู้ยืมแก่สหกรณ์";
            }
            else if (reportCode == "ช3710")
            {
                ViewBag.reportName = "รายงานเงินสดและเงินฝากสถาบันการเงิน";
            }
            return View();
        }
        public ActionResult Export()
        {
            string reportCode = Request["reportCode"];
            string company = Request["SelectCompany"];
            string year = Request["selectYear"];
            string dateAsOf = Request["dateAsOf"];
            //Request["reportCode"]
            ViewBag.company = company;
            ViewBag.year = year;
            ViewBag.dateAsOf = dateAsOf;


            ReportR3Criteria crit = new ReportR3Criteria()
            {
                company = ViewBag.company,
                year = year,
                dateAsOf = dateAsOf
            };

            ReportServiceClient reportWebserviceClient = new ReportServiceClient();

            AutoMapper.Mapper.CreateMap<ReportR3Criteria, ReportR3CriteriaDTO>();

            ReportR3CriteriaDTO reportCriteriaDTO = Mapper.Map<ReportR3Criteria, ReportR3CriteriaDTO>(crit);


            if (reportCode == "ว3302" || reportCode == "ช3302")
            {
                /*
                R3302DTO[] headers = reportWebserviceClient.R3302(reportCriteriaDTO);


                List<valueClass> reportRows1 = new List<valueClass>();
                foreach (R3302DTO header in headers)
                {
                    valueClass row = new valueClass()
                    {

                        string1 = header.ISSUE_TYPE

                    };
                    List<valueClass> items = new List<valueClass>();
                    foreach (R3302DetailDTO detail in header.Detail)
                    {

                        String amount17 = detail.AMOUNT_17.ToString();
                        if (amount17 == null)
                        {
                            amount17 = detail.AMOUNT_17_1.ToString();
                        }


                        valueClass subrow1 = new valueClass()
                        {
                            string1 = detail.INVOLVED_PARTY_TYPE_CODE,
                            string2 = detail.ISSUER_NAME,
                            string3 = detail.ISSUER_TYPE_NAME,
                            string4 = detail.NUMBER_TIME,
                            string5 = detail.ISSUE_DATE.ToString("dd/MM/yyyy"),
                            string6 = detail.PHYSICAL_NUMBER.ToString(),
                            string7 = detail.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                            string8 = detail.MATURITY_DATE.ToString("dd/MM/yyyy"),
                            double9 = double.Parse(detail.PHYSICAL_AMOUNT.ToString()),
                            double10 = double.Parse(detail.INTEREST_RATE.ToString()),

                            double11 = double.Parse(detail.AMOUNT_7.ToString()),
                            double12 = double.Parse(detail.NET_AMOUNT_8.ToString()),
                            double13 = double.Parse(detail.AMOUNT_9.ToString()),
                            double14 = double.Parse(detail.AMOUNT_10.ToString()),
                            double15 = double.Parse(detail.AMOUNT_11.ToString()),
                            double16 = double.Parse(detail.AMOUNT_12.ToString()),
                            double17 = double.Parse(detail.AMOUNT_13.ToString()),
                            double18 = double.Parse(detail.AMOUNT_14.ToString()),
                            double19 = double.Parse(detail.AMOUNT_15.ToString()),
                            double20 = double.Parse(detail.AMOUNT_16.ToString()),
                            double21 = double.Parse(amount17),//detail.AMOUNT_17.ToString(),
                            string22 = detail.REMARK_OIC,

                            string23 = detail.ISSUER_NAME,
                            string24 = detail.FIXED_INCOME_FULLNAME_TH,
                            string25 = detail.SYMBOL,
                            string26 = detail.Notes,
                            double27 = double.Parse(detail.Unit.ToString()),
                            double28 = double.Parse(detail.Amortize_Interest.ToString()),
                            double29 = double.Parse(detail.Defered_Interest.ToString()),
                            double30 = double.Parse(detail.Unrealised_gains_per_loss.ToString()),
                            double31 = double.Parse(detail.Market_Yield.ToString()),
                            double32 = double.Parse(detail.Cost_Yield.ToString()),
                            double33 = double.Parse(detail.Modified_duration.ToString()),
                            string34 = detail.Coupon_payment_date.ToString("dd/MM/yyyy"),
                            double35 = double.Parse(detail.Mkt_value_plus_Mduration.ToString()),
                            string36 = detail.Tier1,
                            string37 = detail.Tier2,
                            string38 = detail.Par_plus_Market_Yield,
                            string39 = detail.duration_year_from_Settle_date,
                            string40 = detail.duration_year_from_EOM,
                            string41 = detail.IRR,
                            string42 = detail.Clean_plus_cost_yield,
                            string43 = detail.Mkt_pric_plus_Mkt_yield

                        };
                        items.Add(subrow1);
                    }
                    row.items = items.ToArray();
                    reportRows1.Add(row);
                }

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export1(
                    "ว3302",
                    new valueClass(), reportRows1.ToArray());
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);
               */

                R3302DTO[] headers = reportWebserviceClient.R3302(reportCriteriaDTO);

                R3302DTO header = headers[0];

                List<valueClass> reportRows1 = new List<valueClass>();
                List<valueClass> reportRows2 = new List<valueClass>();
                List<valueClass> reportRows3 = new List<valueClass>();
                List<valueClass> reportRows4 = new List<valueClass>();
                List<valueClass> reportRows5 = new List<valueClass>();

                subR3302(header.Detail, reportRows1);
                subR3302(header.Detail2, reportRows2);
                subR3302(header.Detail3, reportRows3);
                subR3302(header.Detail4, reportRows4);


                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export4(
                    "ว3302",
                    new valueClass()
                    , reportRows1.ToArray()
                    , reportRows2.ToArray()
                    , reportRows3.ToArray()
                    , reportRows4.ToArray()
                    );
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);
            }
            else if (reportCode == "ว3303" || reportCode == "ว3303")
            {
                /*
                R3303DTO[] headers = reportWebserviceClient.R3303(reportCriteriaDTO);


                List<valueClass> reportRows1 = new List<valueClass>();
                foreach (R3303DTO level1 in headers)
                {
                    valueClass row = new valueClass() { string1 = "lv1 " + level1.ISSUER_TYPE };
                    List<valueClass> itemsL2 = new List<valueClass>();
                    List<valueClass> itemsL3 = new List<valueClass>();
                    List<valueClass> itemsL4 = new List<valueClass>();

                    foreach (R3303DTO level2 in level1.Detail)
                    {
                        valueClass subrow1 = new valueClass() { string1 = "lv2 " + level2.ASSET_CLASS_EN };
                        itemsL2.Add(subrow1);
                        if (level2.Detail.Length > 0)
                        {
                            //ถ้ามี level 3
                            foreach (R3303DTO level3 in level2.Detail)
                            {
                                valueClass subrow2 = new valueClass() { string1 = "lv3 " + level3.RELATED };
                                itemsL3.Add(subrow2);
                                foreach (R3303DTO level4 in level3.Detail)
                                {
                                    //--- is Mapping level 4
                                    valueClass subrow3 = new valueClass()
                                    {
                                        string1 = level4.INVOLVED_PARTY_TYPE_CODE,
                                        string2 = level4.ISSUER_NAME,
                                        string3 = level4.GUARANTOR_NAME,
                                        string4 = level4.ISSUER_RATING,
                                        string5 = level4.SYMBOL,
                                        string6 = level4.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                                        string7 = level4.MATURITY_DATE.ToString("dd/MM/yyyy"),
                                        double8 = double.Parse(level4.YIELD.ToString()),
                                        double9 = double.Parse(level4.Amount_8.ToString()),
                                        double10 = double.Parse(level4.AMOUNT_9.ToString()),
                                        double11 = double.Parse(level4.AMOUNT_10.ToString()),
                                        double12 = double.Parse(level4.AMOUNT_11.ToString()),
                                        double13 = double.Parse(level4.AMOUNT_12.ToString()),
                                        double14 = double.Parse(level4.AMOUNT_13.ToString()),
                                        double15 = double.Parse(level4.AMOUNT_14.ToString()),
                                        double16 = double.Parse(level4.AMOUNT_15.ToString()),
                                        double17 = double.Parse(level4.AMOUNT_16.ToString()),
                                        double18 = double.Parse(level4.AMOUNT_17.ToString()),
                                        string19 = level4.REMARK_OIC,


                                        string20 = level4.Branch,
                                        string21 = level4.AC_number_Voch_no,
                                        string22 = level4.Interest,
                                        double23 = double.Parse(level4.Term.ToString()),
                                        string24 = level4.Coupon_Payment_Date.ToString("dd/MM/yyyy"),//detail.AMOUNT_17.ToString(),
                                        string25 = level4.ISSUER_RATING,
                                        double26 = double.Parse(level4.Par_Plus_coupon.ToString()),
                                        double27 = double.Parse(level4.duration_year_from_SettleDate.ToString()),
                                        double28 = double.Parse(level4.duration_year_from_EOM.ToString()),
                                        double29 = double.Parse(level4.duration_month_from_EOM.ToString()),
                                        double30 = double.Parse(level4.duration_day_from_EOM.ToString()),
                                        double31 = double.Parse(level4.amount_plus_duration_year.ToString())
                                    };
                                    itemsL4.Add(subrow3);

                                }
                                subrow2.items = itemsL4.ToArray();
                            }
                            subrow1.items = itemsL3.ToArray();
                        }
                        else
                        {
                            foreach (R3303DTO level4 in level2.Detail2)
                            {
                                // Mapping in level 4
                                valueClass subrow3 = new valueClass()
                                {
                                    string1 = level4.INVOLVED_PARTY_TYPE_CODE,
                                    string2 = level4.ISSUER_NAME,
                                    string3 = level4.GUARANTOR_NAME,
                                    string4 = level4.ISSUER_RATING,
                                    string5 = level4.SYMBOL,
                                    string6 = level4.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                                    string7 = level4.MATURITY_DATE.ToString("dd/MM/yyyy"),
                                    double8 = double.Parse(level4.YIELD.ToString()),
                                    double9 = double.Parse(level4.Amount_8.ToString()),
                                    double10 = double.Parse(level4.AMOUNT_9.ToString()),
                                    double11 = double.Parse(level4.AMOUNT_10.ToString()),
                                    double12 = double.Parse(level4.AMOUNT_11.ToString()),
                                    double13 = double.Parse(level4.AMOUNT_12.ToString()),
                                    double14 = double.Parse(level4.AMOUNT_13.ToString()),
                                    double15 = double.Parse(level4.AMOUNT_14.ToString()),
                                    double16 = double.Parse(level4.AMOUNT_15.ToString()),
                                    double17 = double.Parse(level4.AMOUNT_16.ToString()),
                                    double18 = double.Parse(level4.AMOUNT_17.ToString()),
                                    string19 = level4.REMARK_OIC,


                                    string20 = level4.Branch,
                                    string21 = level4.AC_number_Voch_no,
                                    string22 = level4.Interest,
                                    double23 = double.Parse(level4.Term.ToString()),
                                    string24 = level4.Coupon_Payment_Date.ToString("dd/MM/yyyy"),//detail.AMOUNT_17.ToString(),
                                    string25 = level4.ISSUER_RATING,
                                    double26 = double.Parse(level4.Par_Plus_coupon.ToString()),
                                    double27 = double.Parse(level4.duration_year_from_SettleDate.ToString()),
                                    double28 = double.Parse(level4.duration_year_from_EOM.ToString()),
                                    double29 = double.Parse(level4.duration_month_from_EOM.ToString()),
                                    double30 = double.Parse(level4.duration_day_from_EOM.ToString()),
                                    double31 = double.Parse(level4.amount_plus_duration_year.ToString())
                                };
                                itemsL4.Add(subrow3);

                            }
                            subrow1.items2 = itemsL4.ToArray();
                        }

                    }
                    row.items = itemsL2.ToArray();
                    reportRows1.Add(row);


                }
                */
                R3303DTO[] headers = reportWebserviceClient.R3303(reportCriteriaDTO);

                R3303DTO header = headers[0];

                List<valueClass> reportRows1 = new List<valueClass>();
                List<valueClass> reportRows2 = new List<valueClass>();
                List<valueClass> reportRows3 = new List<valueClass>();
                List<valueClass> reportRows4 = new List<valueClass>();
                List<valueClass> reportRows5 = new List<valueClass>();
                List<valueClass> reportRows6 = new List<valueClass>();
                List<valueClass> reportRows7 = new List<valueClass>();
                List<valueClass> reportRows8 = new List<valueClass>();
                List<valueClass> reportRows9 = new List<valueClass>();
                List<valueClass> reportRows10 = new List<valueClass>();

                subR3303(header.Detail, reportRows1);
                subR3303(header.Detail2, reportRows2);
                subR3303(header.Detail3, reportRows3);
                subR3303(header.Detail4, reportRows4);
                subR3303(header.Detail5, reportRows5);
                subR3303(header.Detail6, reportRows6);
                subR3303(header.Detail7, reportRows7);
                subR3303(header.Detail8, reportRows8);
                subR3303(header.Detail9, reportRows9);
                subR3303(header.Detail10, reportRows10);

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export10(
                    "ว3303",
                    new valueClass()
                    , reportRows1.ToArray()
                    , reportRows2.ToArray()
                    , reportRows3.ToArray()
                    , reportRows4.ToArray()
                    , reportRows5.ToArray()
                    , reportRows6.ToArray()
                    , reportRows7.ToArray()
                    , reportRows8.ToArray()
                    , reportRows9.ToArray()
                    , reportRows10.ToArray()
                    );
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);

            }
            else if (reportCode == "ว3306" || reportCode == "ช3306")
            {/*
                R3306DTO[] headers = reportWebserviceClient.R3306(reportCriteriaDTO);


                List<valueClass> reportRows1 = new List<valueClass>();
                foreach (R3306DTO level1 in headers)
                {
                    valueClass row = new valueClass() { string1 = "lv1 " + level1.ISSUER_TYPE };
                    List<valueClass> itemsL2 = new List<valueClass>();
                    List<valueClass> itemsL3 = new List<valueClass>();
                    //List<valueClass> itemsL4 = new List<valueClass>();

                    foreach (R3306DTO level2 in level1.Detail)
                    {
                        valueClass subrow1 = new valueClass() { string1 = "lv2 " + level2.RELATED };
                        itemsL2.Add(subrow1);

                        //ถ้ามี level 3
                        foreach (R3306DTO level3 in level2.Detail)
                        {
                            valueClass subrow2 = new valueClass()
                            {
                                string1 = "lv3 " + level3.INVOLVED_PARTY_TYPE_CODE,
                                string2 = level3.ISSUER_NAME,
                                string3 = level3.GUARANTOR_NAME,
                                string4 = level3.GUARANTOR_TYPE_NAME,
                                string5 = level3.ISSUE_RATING,
                                string6 = level3.SYMBOL,
                                string7 = level3.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                                string8 = level3.ISSUE_DATE.ToString("dd/MM/yyyy"),
                                string9 = level3.MATURITY_DATE.ToString("dd/MM/yyyy"),
                                string10 = level3.SYMBOL,
                                string11 = level3.unit.ToString(),
                                string12 = level3.PERCENT_UNIT.ToString(),
                                double13 = double.Parse(level3.YIELD.ToString()),
                                double14 =double.Parse( level3.Amount_11.ToString()),
                                double15 =double.Parse( level3.Amount_12.ToString()),
                                double16 =double.Parse( level3.Amount_13.ToString()),
                                double17 =double.Parse( level3.AMOUNT_14.ToString()),
                                double18 =double.Parse( level3.AMOUNT_15.ToString()),
                                double19 =double.Parse( level3.AMOUNT_16.ToString()),
                                double20 =double.Parse( level3.AMOUNT_17.ToString()),
                                double21 =double.Parse( level3.AMOUNT_18.ToString()),
                                double22 = double.Parse(level3.AMOUNT_19.ToString()),
                                double23 = double.Parse(level3.AMOUNT_20.ToString()),
                                double24 = double.Parse(level3.AMOUNT_21.ToString()),
                                double25 =double.Parse( level3.AMOUNT_22.ToString()),
                                double26 = double.Parse(level3.AMOUNT_23.ToString()),
                                string27 = level3.REMARK_OIC,

                                double28 = double.Parse(level3.Unrealised_gains_divide_loss.ToString()),
                                double29 = double.Parse(level3.Market_Yield.ToString()),
                                double30 = double.Parse(level3.Cost_Yield.ToString()),
                                double31 = double.Parse(level3.Modified_duration.ToString()),
                                double32 = double.Parse(level3.Par_plus_Market_Yield.ToString()),
                                double33 =double.Parse( level3.duration_year_from_Settle_date.ToString()),
                                double34 =double.Parse( level3.duration_year_from_EOM.ToString()),
                                double35 =double.Parse( level3.Clean_plus_cost_yield.ToString()),
                                double36 =double.Parse( level3.Mkt_price_plus_Mkt_yield.ToString()),
                                double37 =double.Parse( level3.Keep_AT.ToString())
                            };
                            itemsL3.Add(subrow2);

                        }
                        subrow1.items = itemsL3.ToArray();


                    }
                    row.items = itemsL2.ToArray();
                    reportRows1.Add(row);


                }

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export1(
                    "ว3306",
                    new valueClass(), reportRows1.ToArray());
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);
                */

                R3306DTO[] headers = reportWebserviceClient.R3306(reportCriteriaDTO);

                R3306DTO header = headers[0];

                List<valueClass> reportRows1 = new List<valueClass>();
                List<valueClass> reportRows2 = new List<valueClass>();
                List<valueClass> reportRows3 = new List<valueClass>();
                List<valueClass> reportRows4 = new List<valueClass>();
                List<valueClass> reportRows5 = new List<valueClass>();
            
                subR3306(header.Detail, reportRows1);
                subR3306(header.Detail2, reportRows2);
                subR3306(header.Detail3, reportRows3);
                subR3306(header.Detail4, reportRows4);
                subR3306(header.Detail5, reportRows5);
             

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export5(
                    "ว3306",
                    new valueClass()
                    , reportRows1.ToArray()
                    , reportRows2.ToArray()
                    , reportRows3.ToArray()
                    , reportRows4.ToArray()
                    , reportRows5.ToArray()
                    );
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);
            }
            else if (reportCode == "ว3305" || reportCode == "ช3305")
            {
                R3305DTO[] headers = reportWebserviceClient.R3305(reportCriteriaDTO);


                List<valueClass> reportRows1 = new List<valueClass>();
                foreach (R3305DTO level1 in headers)
                {
                    valueClass row = new valueClass() { string1 = "lv1 " + level1.ISSUER_TYPE };
                    List<valueClass> itemsL2 = new List<valueClass>();
                    List<valueClass> itemsL3 = new List<valueClass>();
                    //List<valueClass> itemsL4 = new List<valueClass>();

                    foreach (R3305DTO level2 in level1.Detail)
                    {
                        valueClass subrow1 = new valueClass() { string1 = "lv2 " + level2.RELATED };
                        itemsL2.Add(subrow1);

                        //ถ้ามี level 3
                        foreach (R3305DTO level3 in level2.Detail)
                        {
                            valueClass subrow2 = new valueClass()
                            {
                                string1 = "lv3 " + level3.ISSUER_1,
                                string2 = level3.Purchase_Date_2,
                                double3 = double.Parse( level3.Unit_Amount_3.ToString()),
                                double4 = double.Parse( level3.Percent_Unit_Per_Total_4.ToString()),
                                double5 = double.Parse( level3.Par_Amount_5.ToString()),
                                double6 = double.Parse( level3.Paid_Amount_6.ToString()),
                                double7 = double.Parse( level3.Announce_Booking_Amount_7.ToString()),
                                double8 = double.Parse( level3.Purchase_Amount_8.ToString()),
                                double9 = double.Parse( level3.Book_DuringYear_Amount_9.ToString()),
                                double10 =double.Parse(  level3.Book_Appraisal_Amount_10.ToString()),
                                double11 = double.Parse( level3.Book_Net_Amount_11.ToString()),
                                double12 = double.Parse( level3.BV_Amount_12.ToString()),
                                double13 = double.Parse( level3.Quatation_Amount_13.ToString()),
                                double14 = double.Parse( level3.Quatation_BV_Ratio_14.ToString()),
                                double15 = double.Parse( level3.Estimate_Amount_15.ToString()),
                                double16 =double.Parse(  level3.Obligation_Amount_16.ToString()),
                                double17 = double.Parse( level3.Asset_Section27_4_17.ToString()),
                                string18 = level3.Remark_OIC_18,
                                double19 = double.Parse( level3.CostPrice_19.ToString()),
                                double20 = double.Parse( level3.Average_20.ToString()),
                                double21 = double.Parse( level3.Unrealised_div_share_21.ToString()),
                                double22 = double.Parse( level3.Unrealised_22.ToString()),
                                double23 = double.Parse(level3.Percent_Change_23.ToString()),
                                double24 = double.Parse( level3.KeepAt_24),

                            };
                            itemsL3.Add(subrow2);

                        }
                        subrow1.items = itemsL3.ToArray();


                    }
                    row.items = itemsL2.ToArray();
                    reportRows1.Add(row);


                }

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export1(
                    "ว3305",
                    new valueClass(), reportRows1.ToArray());
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);

            }

            else if (reportCode == "ว3307" || reportCode == "ช3307")
            {
                R3307DTO[] headers = reportWebserviceClient.R3307(reportCriteriaDTO);


                List<valueClass> reportRows1 = new List<valueClass>();
                foreach (R3307DTO level1 in headers)
                {
                    valueClass row = new valueClass() { string1 = "lv1 " + level1.ISSUER_TYPE };
                    List<valueClass> itemsL2 = new List<valueClass>();
                    List<valueClass> itemsL3 = new List<valueClass>();
                    //List<valueClass> itemsL4 = new List<valueClass>();

                    foreach (R3307DTO level2 in level1.Detail)
                    {
                        valueClass subrow1 = new valueClass() { string1 = "lv2 " + level2.RELATED };
                        itemsL2.Add(subrow1);

                        //ถ้ามี level 3
                        foreach (R3307DTO level3 in level2.Detail)
                        {
                            valueClass subrow2 = new valueClass()
                            {
                                string1 = "lv3 " + level3.ISSUER_1,
                                string2 = level3.Purchase_Date_2,
                                double3 =double.Parse( level3.Unit_Amount_3.ToString()),
                                double4 =double.Parse( level3.Percent_Unit_Per_Total_4.ToString()),
                                double5 =double.Parse( level3.Par_Amount_5.ToString()),
                                double6 =double.Parse( level3.Announce_Booking_Amount_6.ToString()),
                                double7 =double.Parse( level3.Purchase_Amount_7.ToString()),
                                double8 = double.Parse(level3.Book_DuringYear_Amount_8.ToString()),
                                double9 = double.Parse(level3.Book_Appraisal_Amount_9.ToString()),
                                double10 = double.Parse(level3.Book_Net_Amount_10.ToString()),
                                double11 =double.Parse( level3.NAV_Amount_11.ToString()),
                                double12 = double.Parse(level3.Quatation_NAV_Ratio_12.ToString()),
                                double13 =double.Parse( level3.Estimate_Amount_13.ToString()),
                                double14 = double.Parse(level3.Obligation_Amount_14.ToString()),
                                double15 =double.Parse( level3.Asset_Section27_4_15.ToString()),
                                string16 = level3.Remark_OIC_16,
                                double17 = double.Parse(level3.Cost_17.ToString()),
                                double18 =double.Parse( level3.Price_at_reclassified_date_18.ToString()),
                                double19 =double.Parse( level3.Unrealised_Profit_19.ToString()),
                                double20 =double.Parse( level3.Purchasingdate_20),
                                double21 = double.Parse(level3.Cost_per_Unit_21.ToString()),
                                string22 = level3.Remarks_22,
                                string23 = level3.Type_23,


                            };
                            itemsL3.Add(subrow2);

                        }
                        subrow1.items = itemsL3.ToArray();


                    }
                    row.items = itemsL2.ToArray();
                    reportRows1.Add(row);


                }

                //.xls
                ExcelWSService.ExcelWSClient excelWebServicesClient = new ExcelWSService.ExcelWSClient();

                string path = excelWebServicesClient.export1(
                    "ว3307",
                    new valueClass(), reportRows1.ToArray());
                String link = ConfigurationManager.AppSettings["ExcelServiceOutputUrl"] + path;
                return Redirect(link);

            }
            return View();

        }

        private void subR3302(R3302DTO[] details, List<valueClass> reportRows1)
        {
            foreach (R3302DTO detail in details)
            {

                String amount17 = detail.AMOUNT_17.ToString();
                if (amount17 == null)
                {
                    amount17 = detail.AMOUNT_17_1.ToString();
                }


                valueClass subrow1 = new valueClass()
                {
                    string1 = detail.INVOLVED_PARTY_TYPE_CODE,
                    string2 = detail.ISSUER_NAME,
                    string3 = detail.ISSUER_TYPE_NAME,
                    string4 = detail.NUMBER_TIME,
                    string5 = detail.ISSUE_DATE.ToString("dd/MM/yyyy"),
                    string6 = detail.PHYSICAL_NUMBER.ToString(),
                    string7 = detail.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                    string8 = detail.MATURITY_DATE.ToString("dd/MM/yyyy"),
                    double9 = double.Parse(detail.PHYSICAL_AMOUNT.ToString()),
                    double10 = double.Parse(detail.INTEREST_RATE.ToString()),

                    double11 = double.Parse(detail.AMOUNT_7.ToString()),
                    double12 = double.Parse(detail.NET_AMOUNT_8.ToString()),
                    double13 = double.Parse(detail.AMOUNT_9.ToString()),
                    double14 = double.Parse(detail.AMOUNT_10.ToString()),
                    double15 = double.Parse(detail.AMOUNT_11.ToString()),
                    double16 = double.Parse(detail.AMOUNT_12.ToString()),
                    double17 = double.Parse(detail.AMOUNT_13.ToString()),
                    double18 = double.Parse(detail.AMOUNT_14.ToString()),
                    double19 = double.Parse(detail.AMOUNT_15.ToString()),
                    double20 = double.Parse(detail.AMOUNT_16.ToString()),
                    double21 = double.Parse(amount17),//detail.AMOUNT_17.ToString(),
                    string22 = detail.REMARK_OIC,

                    string23 = detail.ISSUER_NAME,
                    string24 = detail.FIXED_INCOME_FULLNAME_TH,
                    string25 = detail.SYMBOL,
                    string26 = detail.Notes,
                    double27 = double.Parse(detail.Unit.ToString()),
                    double28 = double.Parse(detail.Amortize_Interest.ToString()),
                    double29 = double.Parse(detail.Defered_Interest.ToString()),
                    double30 = double.Parse(detail.Unrealised_gains_per_loss.ToString()),
                    double31 = double.Parse(detail.Market_Yield.ToString()),
                    double32 = double.Parse(detail.Cost_Yield.ToString()),
                    double33 = double.Parse(detail.Modified_duration.ToString()),
                    string34 = detail.Coupon_payment_date.ToString("dd/MM/yyyy"),
                    double35 = double.Parse(detail.Mkt_value_plus_Mduration.ToString()),
                    string36 = detail.Tier1,
                    string37 = detail.Tier2,
                    string38 = detail.Par_plus_Market_Yield,
                    string39 = detail.duration_year_from_Settle_date,
                    string40 = detail.duration_year_from_EOM,
                    string41 = detail.IRR,
                    string42 = detail.Clean_plus_cost_yield,
                    string43 = detail.Mkt_pric_plus_Mkt_yield

                };
                reportRows1.Add(subrow1);
            }
        }

        private void subR3306(R3306DTO[] detail, List<valueClass> reportRows1)
        {
            foreach (R3306DTO level3 in detail)
            {
                valueClass subrow2 = new valueClass()
                {
                    string1 = level3.INVOLVED_PARTY_TYPE_CODE,
                    string2 = level3.ISSUER_NAME,
                    string3 = level3.GUARANTOR_NAME,
                    string4 = level3.GUARANTOR_TYPE_NAME,
                    string5 = level3.ISSUE_RATING,
                    string6 = level3.SYMBOL,
                    string7 = level3.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                    string8 = level3.ISSUE_DATE.ToString("dd/MM/yyyy"),
                    string9 = level3.MATURITY_DATE.ToString("dd/MM/yyyy"),
                    string10 = level3.SYMBOL,
                    string11 = level3.unit.ToString(),
                    string12 = level3.PERCENT_UNIT.ToString(),
                    double13 = double.Parse(level3.YIELD.ToString()),
                    double14 = double.Parse(level3.Amount_11.ToString()),
                    double15 = double.Parse(level3.Amount_12.ToString()),
                    double16 = double.Parse(level3.Amount_13.ToString()),
                    double17 = double.Parse(level3.AMOUNT_14.ToString()),
                    double18 = double.Parse(level3.AMOUNT_15.ToString()),
                    double19 = double.Parse(level3.AMOUNT_16.ToString()),
                    double20 = double.Parse(level3.AMOUNT_17.ToString()),
                    double21 = double.Parse(level3.AMOUNT_18.ToString()),
                    double22 = double.Parse(level3.AMOUNT_19.ToString()),
                    double23 = double.Parse(level3.AMOUNT_20.ToString()),
                    double24 = double.Parse(level3.AMOUNT_21.ToString()),
                    double25 = double.Parse(level3.AMOUNT_22.ToString()),
                    double26 = double.Parse(level3.AMOUNT_23.ToString()),
                    string27 = level3.REMARK_OIC,

                    double28 = double.Parse(level3.Unrealised_gains_divide_loss.ToString()),
                    double29 = double.Parse(level3.Market_Yield.ToString()),
                    double30 = double.Parse(level3.Cost_Yield.ToString()),
                    double31 = double.Parse(level3.Modified_duration.ToString()),
                    double32 = double.Parse(level3.Par_plus_Market_Yield.ToString()),
                    double33 = double.Parse(level3.duration_year_from_Settle_date.ToString()),
                    double34 = double.Parse(level3.duration_year_from_EOM.ToString()),
                    double35 = double.Parse(level3.Clean_plus_cost_yield.ToString()),
                    double36 = double.Parse(level3.Mkt_price_plus_Mkt_yield.ToString()),
                    double37 = double.Parse(level3.Keep_AT.ToString())
                };
                reportRows1.Add(subrow2);

            }
        }

        private static void subR3303(R3303DTO[] detail, List<valueClass> reportRows1)
        {
            foreach (R3303DTO level4 in detail)
            {

                valueClass row = new valueClass()
                {
                    string1 = level4.INVOLVED_PARTY_TYPE_CODE,
                    string2 = level4.ISSUER_NAME,
                    string3 = level4.GUARANTOR_NAME,
                    string4 = level4.ISSUER_RATING,
                    string5 = level4.SYMBOL,
                    string6 = level4.SETTLEMENT_DATE.ToString("dd/MM/yyyy"),
                    string7 = level4.MATURITY_DATE.ToString("dd/MM/yyyy"),
                    double8 = double.Parse(level4.YIELD.ToString()),
                    double9 = double.Parse(level4.Amount_8.ToString()),
                    double10 = double.Parse(level4.AMOUNT_9.ToString()),
                    double11 = double.Parse(level4.AMOUNT_10.ToString()),
                    double12 = double.Parse(level4.AMOUNT_11.ToString()),
                    double13 = double.Parse(level4.AMOUNT_12.ToString()),
                    double14 = double.Parse(level4.AMOUNT_13.ToString()),
                    double15 = double.Parse(level4.AMOUNT_14.ToString()),
                    double16 = double.Parse(level4.AMOUNT_15.ToString()),
                    double17 = double.Parse(level4.AMOUNT_16.ToString()),
                    double18 = double.Parse(level4.AMOUNT_17.ToString()),
                    string19 = level4.REMARK_OIC,


                    string20 = level4.Branch,
                    string21 = level4.AC_number_Voch_no,
                    string22 = level4.Interest,
                    double23 = double.Parse(level4.Term.ToString()),
                    string24 = level4.Coupon_Payment_Date.ToString("dd/MM/yyyy"),//detail.AMOUNT_17.ToString(),
                    string25 = level4.ISSUER_RATING,
                    double26 = double.Parse(level4.Par_Plus_coupon.ToString()),
                    double27 = double.Parse(level4.duration_year_from_SettleDate.ToString()),
                    double28 = double.Parse(level4.duration_year_from_EOM.ToString()),
                    double29 = double.Parse(level4.duration_month_from_EOM.ToString()),
                    double30 = double.Parse(level4.duration_day_from_EOM.ToString()),
                    double31 = double.Parse(level4.amount_plus_duration_year.ToString())
                };
                reportRows1.Add(row);

            }
        }
    }
}
